import pymysql



#创建股票表结构
def create_table(db,code):
    cur = db.cursor()
    str_create_stock_table ="create table if not EXISTS %s(date date not null COMMENT 'date',"\
	     "open float not null COMMENT 'open price',"\
		 "high float not null comment 'highest price',"\
       	 "low float not null comment 'lowest price',"\
         "close float not null comment 'close price',"\
         "volume BIGINT(20) not null comment 'deal amount',"\
         "amount BIGINT(20) not null comment 'deal money',"\
         "turn float not null comment 'change hand rate',"\
         "pctChg float not null comment 'rise fall range',"\
         "PRIMARY KEY USING BTREE (date))CHARACTER SET = utf8"%code
    cur.execute(str_create_stock_table)
    db.commit()

#链接数据库
def connect():
    try:
        db=pymysql.connect(host='localhost',user='root',password='test123',database='stock_day_data')    
        #str_create_stock_table ="create table if not EXISTS 60034(data date not null COMMENT '日期',"\
	    #  "open float not null COMMENT '开盘价',"\
		#  "high float not null comment '最高价',"\
       	#  "low float not null comment '最低价',"\
        #  "close float not null comment '收盘价',"\
        #  "volume BIGINT(20) not null comment '成交量',"\
        #  "amount BIGINT(20) not null comment '成交金额',"\
        #  "turn float not null comment '换手率',"\
        #  "pctChg float not null comment '涨跌幅',"\
        #  "PRIMARY KEY USING BTREE (data))"         
        return db
    except pymysql.Error as e:
        print("数据库连接错误" + str(e))

#关闭数据库
def close(db):
      cur= db.cursor(); 
      cur.close()
      db.close()
      
#查找指定股票是否有指定日期的数据
def find_stock_day_data(db, code, day):
    cur = db.cursor()
    str_find_stock_day_data ="SELECT 1 from %s WHERE date ='%s' LIMIT 1"%(code,day)
    result = cur.execute(str_find_stock_day_data)
    db.commit()
    return result

#添加指定股票指定日期的数据
def add_stock_day_data(db, code, stockdata):
    cur = db.cursor()
    str_add_stock_day_data="INSERT into %s(date,open,high,low,close,volume,amount,turn,pctChg) VALUES('%s',%s,%s,%s,%s,%s,%s,%s,%s)"\
    %(code,stockdata['date'],stockdata['open'],stockdata['high'],stockdata['low'],stockdata['close'],stockdata['volume'],stockdata['amount'],stockdata['turn'],stockdata['pctChg'])
    
    cur.execute(str_add_stock_day_data)
    db.commit()







        



 
     